MySQL ロック挙動確認

2つ terminal 開いてうつのは面倒でこういう感じでやると楽か?

単に2つのコネクション横断してシーケンシャルにクエリ実行したいだけ

code:lock.sh

cat <<EOS | mysql -uroot

CREATE DATABASE IF NOT EXISTS lock_test;

USE lock_test;

DROP TABLE IF EXISTS example;

CREATE TABLE example (

id bigint unsigned NOT NULL,

name VARCHAR(10) NOT NULL,

updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

EOS

cat <<EOS > query1.sql

USE lock_test;

INSERT INTO example SET id = 1, name = 'hoge';

INSERT INTO example SET id = 2, name = 'fuga';

BEGIN;

\! echo 1: SELECT ... FOR UPDATE

SELECT * FROM example WHERE id = 1 FOR UPDATE;

DO SLEEP(2);

\! echo 1: UPDATE

UPDATE example SET name = 'hogehoge' WHERE id = 1;

\! echo 1: UPDATE done

DO SLEEP(2);

COMMIT;

EOS

cat <<EOS > query2.sql

USE lock_test;

DO SLEEP(1);

BEGIN;

\! echo 2: SELECT ... FOR UPDATE \(waiting\)

SELECT * FROM example WHERE id = 1 FOR UPDATE;

\! echo 2: SELECT done

DO SLEEP(2);

COMMIT;

EOS

mysql -uroot -t -e 'source ./query1.sql' &

mysql -uroot -t -e 'source ./query2.sql' &

wait

echo '====='

cat <<EOS | mysql -uroot -t

USE lock_test;

SELECT * FROM example;

EOS